from socket import *
from threading import Thread
import pymysql


class Data:
    def __init__(self):
        self.db = pymysql.connect(user="devil", password="123456", database="dict", charset="utf8")
        self.cur = self.db.cursor()

    def close(self):
        self.cur.close()
        self.db.close()

    def insert_stu(self, stu_id, name, score):
        sql = "insert into stu3(id, name, score) values (%s, %s, %s)"
        try:
            self.cur.execute(sql, [stu_id, name, score])
            self.db.commit()
            return True
        except:
            self.db.rollback()
            return False

    def del_stu(self, stu_id):
        sql = "delete from stu3 where id=%s"
        try:
            self.cur.execute(sql, [stu_id])
            self.db.commit()
            return True
        except:
            self.db.rollback()
            return False

    def update_stu(self, stu_id, name, score):
        sql = "update stu3 set name=%s,score=%s where id=%s"
        try:
            self.cur.execute(sql, [name, score, stu_id])
            self.db.commit()
            return True
        except:
            self.db.rollback()
            return False


class Handle(Thread):
    def __init__(self, conn, addr):
        self.conn = conn
        self.address = addr
        self.db = Data()
        super().__init__()

    # 处理客户端请求
    def run(self):
        while True:
            request = self.conn.recv(1024)
            if not request:
                break
            tmp = request.decode().split(" ")
            print(tmp)
            if tmp[0] == "add":
                self.add_stu(stu_id=tmp[1], name=tmp[2], score=tmp[3])
            if tmp[0] == "update":
                self.update_stu(stu_id=tmp[1], name=tmp[2], score=tmp[3])
            if tmp[0] == "del":
                self.del_stu(stu_id=tmp[1])
        self.conn.close()
        self.db.close()

    def add_stu(self, stu_id, name, score):
        if self.db.insert_stu(stu_id, name, score):
            self.conn.send(b"OK")
        else:
            self.conn.send(b"Defeat")

    def del_stu(self, stu_id):
        if self.db.del_stu(stu_id):
            self.conn.send(b"OK")
        else:
            self.conn.send(b"Defeat")

    def update_stu(self, stu_id, name, score):
        if self.db.update_stu(stu_id, name, score):
            self.conn.send(b"OK")
        else:
            self.conn.send(b"Defeat")


class ThreadServer:
    def __init__(self, host="", port=0):
        self.host = host
        self.port = port
        self.address = (host, port)
        self.sock = self._create_socket()

    def _create_socket(self):
        sock = socket()
        sock.bind(self.address)
        return sock

    # 启动服务
    def serve_forever(self):
        self.sock.listen()
        print("Listen the port :", self.port)
        while True:
            conn, addr = self.sock.accept()
            print("Connect from :", addr)
            t = Handle(conn, addr)
            t.start()


if __name__ == '__main__':
    server = ThreadServer(host="0.0.0.0", port=8888)
    server.serve_forever()
